﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using Phoenix.AppCode.Entities;
using System.Data.SqlClient;
using System.Data;

namespace Phoenix.AppCode.Data
{
    public class ArticleDAO : BaseDAO
    {
        public DataTable ArticleGetById (Article artilce)
        {
            using (SqlConnection connection = GetConnection())
            {
                connection.Open();
                SqlCommand command = new SqlCommand("sp_ArticleListByIdCat", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_ArticleCategory", artilce.ID_ArticleCategory));
                command.Parameters.Add(new SqlParameter("@Language", artilce.Language));
                SqlDataAdapter sda = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                command.ExecuteNonQuery();
                return dt;
            }
        }
        public DataTable ArticleDetails(Article artilce)
        {
            using (SqlConnection connection = GetConnection())
            {
                connection.Open();
                SqlCommand command = new SqlCommand("sp_ArticleDetails", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID", artilce.ID_Article));
                command.Parameters.Add(new SqlParameter("@Language", artilce.Language));
                SqlDataAdapter sda = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                command.ExecuteNonQuery();
                return dt;
            }
        }
        public DataTable ArticleLoadService(Article artilce)
        {
            using (SqlConnection connection = GetConnection())
            {
                connection.Open();
                SqlCommand command = new SqlCommand("sp_ArticleService", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID", artilce.ID_ArticleCategory));
                command.Parameters.Add(new SqlParameter("@Language", artilce.Language));
                SqlDataAdapter sda = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                command.ExecuteNonQuery();
                return dt;
            }
        }


        #region Backend method
        /// <summary>
        /// Insert Article
        /// </summary>
        /// <param name="article"></param>
        public void Create(Article article)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spArticleCreate", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_ArticleCategory", article.ID_ArticleCategory));
                command.Parameters.Add(new SqlParameter("@TitleName_vi", article.TitleName_vi));
                command.Parameters.Add(new SqlParameter("@TitleName_en", article.TitleName_en));
                command.Parameters.Add(new SqlParameter("@Content_vi", article.Content_vi));
                command.Parameters.Add(new SqlParameter("@Content_en", article.Content_en));
                command.Parameters.Add(new SqlParameter("@PostUser", article.PostUser));
                command.Parameters.Add(new SqlParameter("@EditUser", article.EditUser));
                command.Parameters.Add(new SqlParameter("@TitleHead_vi", article.TitleHead_vi));
                command.Parameters.Add(new SqlParameter("@TitleHead_en", article.TitleHead_en));
                command.Parameters.Add(new SqlParameter("@DescriptionHead_vi", article.DescriptionHead_vi));
                command.Parameters.Add(new SqlParameter("@DescriptionHead_en", article.DescriptionHead_en));
                command.Parameters.Add(new SqlParameter("@KeywordHead_vi", article.KeywordHead_vi));
                command.Parameters.Add(new SqlParameter("@KeywordHead_en", article.KeywordHead_en));
                command.Parameters.Add(new SqlParameter("@UrlHead_vi", article.UrlHead_vi));
                command.Parameters.Add(new SqlParameter("@UrlHead_en", article.UrlHead_en));

                connection.Open();
                command.ExecuteNonQuery();
                //if (paramId.Value != DBNull.Value)
                //article.ID = (long)paramId.Value;
                //else
                //  throw new DataAccessException(String.Format(Resources.Strings.CreateEntityException, "Category"));
            }
        }

        /// <summary>
        /// Edit Article Category
        /// </summary>
        /// <param name="category"></param>
        public void Edit(Article article)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spArticleEdit", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_Article", article.ID_Article));
                command.Parameters.Add(new SqlParameter("@ID_ArticleCategory", article.ID_ArticleCategory));
                command.Parameters.Add(new SqlParameter("@TitleName_vi", article.TitleName_vi));
                command.Parameters.Add(new SqlParameter("@TitleName_en", article.TitleName_en));
                command.Parameters.Add(new SqlParameter("@Content_vi", article.Content_vi));
                command.Parameters.Add(new SqlParameter("@Content_en", article.Content_en));
                command.Parameters.Add(new SqlParameter("@EditUser", article.EditUser));
                command.Parameters.Add(new SqlParameter("@TitleHead_vi", article.TitleHead_vi));
                command.Parameters.Add(new SqlParameter("@TitleHead_en", article.TitleHead_en));
                command.Parameters.Add(new SqlParameter("@DescriptionHead_vi", article.DescriptionHead_vi));
                command.Parameters.Add(new SqlParameter("@DescriptionHead_en", article.DescriptionHead_en));
                command.Parameters.Add(new SqlParameter("@KeywordHead_vi", article.KeywordHead_vi));
                command.Parameters.Add(new SqlParameter("@KeywordHead_en", article.KeywordHead_en));
                command.Parameters.Add(new SqlParameter("@UrlHead_vi", article.UrlHead_vi));
                command.Parameters.Add(new SqlParameter("@UrlHead_en", article.UrlHead_en));

                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw new DataAccessException(String.Format(Resources.Strings.UpdateEntityException, "Category", category.ID));
            }
        }

        /// <summary>
        /// On/Off view
        /// </summary>
        /// <param name="article"></param>
        public void OnOff(Article article)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spArticleOnOff", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_article", article.ID_Article));

                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw new DataAccessException(String.Format(Resources.Strings.UpdateEntityException, "Category", category.ID));
            }
        }


        /// <summary>
        /// Delete Article Category
        /// </summary>
        /// <param name="category"></param>
        public void Delete(Article article)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spArticleDelete", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_article", article.ID_Article));

                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw new DataAccessException(String.Format(Resources.Strings.DeleteEntityException, "Category", category.ID));
            }
        }

        /// <summary>
        /// Show Article Category All
        /// </summary>
        /// <returns></returns>
        public DataTable ArticleCategoryList(String language)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spArticleCategoryList", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@Language", SqlDbType.NVarChar).Value = language;
                SqlDataAdapter da = new SqlDataAdapter(command);

                connection.Open();
                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
        }

        /// <summary>
        /// Show Article All
        /// </summary>
        /// <returns></returns>
        public DataTable spArticleListByIdCat(int idarticlecategory, String language)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spArticleListByIdCat", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@ID_ArticleCategory", SqlDbType.Int).Value = idarticlecategory;
                command.Parameters.Add("@Language", SqlDbType.NVarChar).Value = language;
                SqlDataAdapter da = new SqlDataAdapter(command);

                connection.Open();
                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
        }

        /// <summary>
        /// Show data by ID
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        public DataTable ArticleList_GetByID(int idarticle)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spArticleEdit_GetByID", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@ID_Article", SqlDbType.Int).Value = idarticle;
                SqlDataAdapter da = new SqlDataAdapter(command);

                connection.Open();
                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
        }

        /// <summary>
        /// Get ID to Edit or Delete
        /// </summary>
        /// <param name="idarticle"></param>
        /// <returns></returns>
        public Article GetArticleByID(int idarticle)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spArticleEdit_GetByID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_Article", idarticle));

                connection.Open();
                Article article = null;
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.Read())
                        article = CreateArticleFromReader(reader);
                    //else
                    //   throw new DataAccessException(String.Format(Resources.Strings.GetEntityException, "Category", ID));
                }

                return article;
            }
        }

        private Article CreateArticleFromReader(IDataReader reader)
        {
            Article article = new Article();
            article.ID_Article = (int)reader["ID_Article"];
            article.ID_ArticleCategory = (int)reader["ID_ArticleCategory"];
            article.TitleName = (string)reader["TitleName"];
            article.DescriptionHead = (string)reader["DescriptionHead"];            
            article.Visible = (Boolean)reader["Visible"];
            article.Language = (string)reader["Language"];
            return article;
        }
        #endregion
    }
}